#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
一键生成完整测试数据
包含：笔记、书评、用户行为数据
基于 MySQL 真实图书数据
"""

from pymongo import MongoClient
import mysql.connector
from datetime import datetime, timedelta
import random
import sys

# ==================== 数据库配置 ====================
MONGO_CONFIG = {
    'host': '192.168.200.131',
    'port': 27017,
    'note_db': 'note_db',        # 笔记数据库
    'review_db': 'review_db',    # 书评数据库
    'behavior_db': 'behavior_db' # 行为数据库
}

MYSQL_CONFIG = {
    'host': '192.168.200.131',
    'user': 'root',
    'password': '1234',
    'book_db': 'book_db',        # 图书数据库
    'user_db': 'user_db'         # 用户数据库
}

# ==================== 数据生成配置 ====================
DATA_CONFIG = {
    'num_users': 50,        # 测试用户数
    'num_notes': 500,       # 笔记数量
    'num_reviews': 300,     # 书评数量
    'num_behaviors': 5000,  # 行为数据数量
    'days_back': 180        # 时间跨度（天）
}

# ==================== 测试用户数据 ====================
TEST_USERS = [
    "书虫小王", "阅读达人", "文学爱好者", "知识探索者", "读书人",
    "求知若渴", "书海泛舟", "文字收藏家", "思想者", "学习狂人",
    "智慧之光", "书香门第", "阅读使者", "知识分子", "文化人",
    "书评家老王", "资深书友", "深度阅读者", "好书推荐官", "阅读推广人",
    "墨香书生", "文艺青年", "博学多才", "才子佳人", "书卷气息",
    "诗书传家", "学富五车", "满腹经纶", "博览群书", "手不释卷",
    "嗜书如命", "书痴", "读书郎", "文化使者", "知识传播者",
    "阅读推手", "书友会会长", "图书管理员", "藏书家", "爱书人",
    "文学青年", "诗词爱好者", "历史迷", "科技控", "哲学思考者",
    "心理学家", "经济学人", "社会观察家", "艺术鉴赏家", "音乐文学家"
]

# 导入笔记和书评生成模板（简化版）
NOTE_TEMPLATES = [
    "读《{title}》有感：{insight}",
    "《{title}》阅读笔记：{insight}",
    "{title} 读后感：{insight}"
]

REVIEW_TEMPLATES_5STAR = [
    "非常棒的一本书！{highlight}。强烈推荐！",
    "这本书改变了我的想法。{highlight}。五星好评！"
]

REVIEW_TEMPLATES_4STAR = [
    "整体不错的一本书。{highlight}。值得一读。",
    "还可以，{highlight}。推荐给感兴趣的朋友。"
]

REVIEW_TEMPLATES_3STAR = [
    "中规中矩吧，{highlight}。仅供参考。",
    "一般般，{highlight}。看个人兴趣。"
]

INSIGHTS = [
    "学到了很多新知识",
    "引发了深刻思考",
    "非常有启发性",
    "理论与实践结合得很好",
    "作者观点独到"
]

HIGHLIGHTS = [
    "内容充实，逻辑清晰",
    "案例丰富，通俗易懂",
    "文笔优美，引人入胜",
    "观点新颖，发人深省"
]

# ==================== 工具函数 ====================

def random_date(start_date, end_date):
    """生成随机日期（确保在今天之前）"""
    time_delta = end_date - start_date
    random_days = random.randint(0, time_delta.days)
    random_seconds = random.randint(0, 86400)
    random_datetime = start_date + timedelta(days=random_days, seconds=random_seconds)
    
    # 确保生成的日期在今天之前
    now = datetime.now()
    if random_datetime >= now:
        # 如果生成的时间在今天或之后，则设置为昨天的随机时间
        yesterday = now - timedelta(days=1)
        random_datetime = yesterday.replace(
            hour=random.randint(0, 23),
            minute=random.randint(0, 59),
            second=random.randint(0, 59)
        )
    
    return random_datetime


def get_books_from_mysql():
    """从MySQL获取图书数据"""
    try:
        conn = mysql.connector.connect(
            host=MYSQL_CONFIG['host'],
            user=MYSQL_CONFIG['user'],
            password=MYSQL_CONFIG['password'],
            database=MYSQL_CONFIG['book_db']  # 使用图书数据库
        )
        cursor = conn.cursor(dictionary=True)
        
        # 查询图书数据（限制数量避免过多）
        # 注意：实际表结构中有 category_id, publisher, description, url
        cursor.execute("""
            SELECT 
                b.id, 
                b.title, 
                b.author, 
                b.cover,
                b.category_id,
                c.name as category,
                b.publisher,
                b.description
            FROM tb_book b
            LEFT JOIN tb_category c ON b.category_id = c.id
            LIMIT 100
        """)
        
        books = cursor.fetchall()
        cursor.close()
        conn.close()
        
        return books
    except Exception as e:
        print(f"   ! 从MySQL获取图书数据失败: {e}")
        print("   - 将使用测试图书数据")
        return []


def generate_test_books():
    """生成测试图书数据（仅在MySQL无法连接时使用）"""
    book_data = [
        ("如何阅读一本书", "莫提默·艾德勒", "文学"),
        ("人类简史", "尤瓦尔·赫拉利", "历史"),
        ("三体", "刘慈欣", "科幻"),
        ("活着", "余华", "文学"),
        ("平凡的世界", "路遥", "文学"),
        ("追风筝的人", "卡勒德·胡赛尼", "文学"),
        ("解忧杂货店", "东野圭吾", "小说"),
        ("白夜行", "东野圭吾", "推理"),
        ("小王子", "圣埃克苏佩里", "童话"),
        ("百年孤独", "加西亚·马尔克斯", "文学"),
    ]
    
    books = []
    for i, (title, author, category) in enumerate(book_data, 1):
        books.append({
            'id': i,
            'title': title,
            'author': author,
            'cover': '',  # 测试数据不使用封面，应该从MySQL图书数据库获取真实封面
            'category': category,
            'category_id': None,
            'publisher': '',
            'description': ''
        })
    
    return books


def generate_users():
    """生成测试用户数据"""
    users = []
    # 统一使用固定头像URL
    avatar_url = 'http://www.qqylp.cn/wp-content/uploads/2023/06/QQ%E5%9B%BE%E7%89%8720230609162827.png'
    
    for i, name in enumerate(TEST_USERS[:DATA_CONFIG['num_users']], 1):
        # 为用户ID=21设置特定的昵称和头像
        if i == 21:
            users.append({
                'id': i,
                'nickname': 'claye',
                'avatar': 'https://thirdwx.qlogo.cn/mmopen/vi_32/PiajxSqBRaEKD2MDIYzW7P3InMTDgxEiayiahJyCWHvawiaVnjZX56lY5FfrFbA8DI0IibzAk9Due6uybJ2UduVGcPVIusGeJ8DAM6L1iaxfSJlZAsS53vDgG48g/132',
                'level': random.randint(1, 10)
            })
        else:
            users.append({
                'id': i,
                'nickname': name,
                'avatar': avatar_url,
                'level': random.randint(1, 10)
            })
    return users


def generate_note(user, book, create_time):
    """生成笔记"""
    author = {
        'id': user['id'],
        'nickname': user['nickname'],
        'avatar': user['avatar'],
        'level': user['level'],
        'isFollowing': False
    }
    
    book_vo = {
        'id': book['id'],
        'title': book['title'],
        'author': book.get('author', '佚名'),
        'cover': book.get('cover', ''),
        'category': book.get('category', ''),
        'categoryId': book.get('category_id'),
        'publisher': book.get('publisher', ''),
        'description': book.get('description', ''),
        'readCount': random.randint(100, 10000),
        'noteCount': random.randint(10, 500),
        'reviewCount': random.randint(5, 200),
        'collectCount': random.randint(50, 5000),
    }
    
    template = random.choice(NOTE_TEMPLATES)
    content = template.format(
        title=book['title'],
        insight=random.choice(INSIGHTS)
    )
    
    return {
        'author': author,
        'book': book_vo,
        'title': f"读《{book['title']}》有感",
        'content': content,  # 使用纯文本，不添加HTML标签
        'images': [],
        'tags': random.sample(['读书笔记', '学习心得', '读后感', '好书推荐'], k=2),
        'status': 1,
        'create_time': create_time,
        'update_time': create_time
    }


def generate_review(user, book, create_time):
    """生成书评"""
    rating = random.choices([5, 4, 3], weights=[0.5, 0.3, 0.2])[0]
    
    if rating == 5:
        template = random.choice(REVIEW_TEMPLATES_5STAR)
    elif rating == 4:
        template = random.choice(REVIEW_TEMPLATES_4STAR)
    else:
        template = random.choice(REVIEW_TEMPLATES_3STAR)
    
    content = template.format(highlight=random.choice(HIGHLIGHTS))
    
    author = {
        'id': user['id'],
        'nickname': user['nickname'],
        'avatar': user['avatar'],
        'level': user['level'],
        'isFollowing': False
    }
    
    book_vo = {
        'id': book['id'],
        'title': book['title'],
        'author': book.get('author', '佚名'),
        'cover': book.get('cover', ''),
        'category': book.get('category', ''),
        'categoryId': book.get('category_id'),
        'publisher': book.get('publisher', ''),
        'description': book.get('description', ''),
        'readCount': random.randint(100, 10000),
        'noteCount': random.randint(10, 500),
        'reviewCount': random.randint(5, 200),
        'collectCount': random.randint(50, 5000),
    }
    
    return {
        'author': author,
        'book': book_vo,
        'content': content,
        'rating': rating,
        'status': 1,
        'create_time': create_time,
        'update_time': create_time
    }


def generate_behavior(user_id, book_id, create_time):
    """生成用户行为"""
    behavior_types = {
        'page_view': (30, 0, 5),
        'book_view': (25, 10, 300),
        'reading': (15, 300, 3600),
        'search': (5, 5, 30),
        'like': (2, 0, 1),
        'collect': (2, 0, 1),
    }
    
    # 按权重选择行为类型
    types = list(behavior_types.keys())
    weights = [v[0] for v in behavior_types.values()]
    behavior_type = random.choices(types, weights=weights)[0]
    
    # 获取持续时长
    _, min_dur, max_dur = behavior_types[behavior_type]
    duration = random.randint(min_dur, max_dur)
    
    # 确定目标类型
    if behavior_type in ['book_view', 'reading']:
        target_type = 'book'
    else:
        target_type = random.choice(['book', 'note', 'review'])
    
    return {
        'user_id': user_id,
        'behavior_type': behavior_type,
        'target_type': target_type,
        'target_id': str(book_id),
        'duration': duration,
        'behavior_data': None,
        'create_time': create_time
    }


# ==================== 主函数 ====================

def main():
    """主函数"""
    print("=" * 70)
    print(" " * 20 + "完整测试数据生成工具")
    print("=" * 70)
    
    # 1. 连接数据库
    print("\n【步骤 1/8】连接数据库")
    print(f"  MongoDB: {MONGO_CONFIG['host']}:{MONGO_CONFIG['port']}")
    print(f"  MySQL:   {MYSQL_CONFIG['host']}:3306")
    
    try:
        mongo_client = MongoClient(
            MONGO_CONFIG['host'],
            MONGO_CONFIG['port'],
            serverSelectionTimeoutMS=5000
        )
        mongo_client.server_info()
        # 获取各个数据库的连接
        note_db = mongo_client[MONGO_CONFIG['note_db']]
        review_db = mongo_client[MONGO_CONFIG['review_db']]
        behavior_db = mongo_client[MONGO_CONFIG['behavior_db']]
        print("  ✓ MongoDB 连接成功")
    except Exception as e:
        print(f"  ✗ MongoDB 连接失败: {e}")
        print("\n请检查：")
        print("  1. MongoDB 服务是否启动")
        print("  2. IP地址和端口是否正确")
        print("  3. 防火墙是否开放27017端口")
        return
    
    # 2. 获取图书数据
    print("\n【步骤 2/8】获取图书数据")
    books = get_books_from_mysql()
    
    if not books:
        print("  ⚠ 警告：无法从MySQL获取图书数据")
        print("  - 使用测试图书数据（无封面）")
        print("  - 建议：确保MySQL中有图书数据以获取真实封面")
        books = generate_test_books()
    else:
        print(f"  ✓ 从MySQL获取了 {len(books)} 本真实图书数据（含封面）")
    
    print(f"  ✓ 准备了 {len(books)} 本图书")
    
    # 3. 生成用户数据
    print("\n【步骤 3/8】生成用户数据")
    users = generate_users()
    print(f"  ✓ 生成了 {len(users)} 个测试用户")
    
    # 4. 数据清理确认
    print("\n【步骤 4/8】数据清理")
    print("  警告: 将清空以下数据库的数据：")
    print("    - note_db.tb_note (笔记)")
    print("    - review_db.tb_review (书评)")
    print("    - behavior_db.user_behavior (用户行为)")
    
    choice = input("\n  是否继续？(y/n): ").strip().lower()
    if choice != 'y':
        print("\n  操作已取消")
        return
    
    # 清空数据
    note_db.tb_note.delete_many({})
    review_db.tb_review.delete_many({})
    behavior_db.user_behavior.delete_many({})
    print("  ✓ 数据清理完成")
    
    # 时间范围（确保所有数据都在今天之前）
    end_date = datetime.now() - timedelta(days=1)  # 结束时间设为昨天
    end_date = end_date.replace(hour=23, minute=59, second=59)  # 设为昨天的最后一秒
    start_date = end_date - timedelta(days=DATA_CONFIG['days_back'])
    
    # 5. 生成笔记数据
    print(f"\n【步骤 5/8】生成笔记数据 (目标: {DATA_CONFIG['num_notes']} 条)")
    notes = []
    for i in range(DATA_CONFIG['num_notes']):
        user = random.choice(users)
        book = random.choice(books)
        create_time = random_date(start_date, end_date)
        
        note = generate_note(user, book, create_time)
        notes.append(note)
        
        if len(notes) >= 100:
            note_db.tb_note.insert_many(notes)
            print(f"  ✓ 已生成 {i + 1}/{DATA_CONFIG['num_notes']} 条笔记")
            notes = []
    
    if notes:
        note_db.tb_note.insert_many(notes)
    
    print(f"  ✓ 笔记数据生成完成")
    
    # 6. 生成书评数据
    print(f"\n【步骤 6/8】生成书评数据 (目标: {DATA_CONFIG['num_reviews']} 条)")
    reviews = []
    for i in range(DATA_CONFIG['num_reviews']):
        user = random.choice(users)
        book = random.choice(books)
        create_time = random_date(start_date, end_date)
        
        review = generate_review(user, book, create_time)
        reviews.append(review)
        
        if len(reviews) >= 50:
            review_db.tb_review.insert_many(reviews)
            print(f"  ✓ 已生成 {i + 1}/{DATA_CONFIG['num_reviews']} 条书评")
            reviews = []
    
    if reviews:
        review_db.tb_review.insert_many(reviews)
    
    print(f"  ✓ 书评数据生成完成")
    
    # 7. 生成用户行为数据
    print(f"\n【步骤 7/8】生成用户行为数据 (目标: {DATA_CONFIG['num_behaviors']} 条)")
    behaviors = []
    for i in range(DATA_CONFIG['num_behaviors']):
        user = random.choice(users)
        book = random.choice(books)
        create_time = random_date(start_date, end_date)
        
        behavior = generate_behavior(user['id'], book['id'], create_time)
        behaviors.append(behavior)
        
        if len(behaviors) >= 500:
            behavior_db.user_behavior.insert_many(behaviors)
            print(f"  ✓ 已生成 {i + 1}/{DATA_CONFIG['num_behaviors']} 条行为")
            behaviors = []
    
    if behaviors:
        behavior_db.user_behavior.insert_many(behaviors)
    
    print(f"  ✓ 用户行为数据生成完成")
    
    # 8. 创建索引
    print("\n【步骤 8/8】创建数据库索引")
    try:
        # 笔记索引
        note_db.tb_note.create_index([('author.id', 1)])
        note_db.tb_note.create_index([('book.id', 1)])
        note_db.tb_note.create_index([('create_time', -1)])
        
        # 书评索引
        review_db.tb_review.create_index([('author.id', 1)])
        review_db.tb_review.create_index([('book.id', 1)])
        review_db.tb_review.create_index([('rating', -1)])
        review_db.tb_review.create_index([('create_time', -1)])
        
        # 行为索引
        behavior_db.user_behavior.create_index([('user_id', 1)])
        behavior_db.user_behavior.create_index([('behavior_type', 1)])
        behavior_db.user_behavior.create_index([('create_time', -1)])
        
        print("  ✓ 索引创建成功")
    except Exception as e:
        print(f"  ! 索引创建警告: {e}")
    
    # 统计报告
    print("\n" + "=" * 70)
    print(" " * 25 + "统计报告")
    print("=" * 70)
    
    print(f"\n📊 数据总览:")
    print(f"  • 笔记数据 (note_db):     {note_db.tb_note.count_documents({}):,} 条")
    print(f"  • 书评数据 (review_db):   {review_db.tb_review.count_documents({}):,} 条")
    print(f"  • 用户行为 (behavior_db): {behavior_db.user_behavior.count_documents({}):,} 条")
    print(f"  • 数据总计:               {note_db.tb_note.count_documents({}) + review_db.tb_review.count_documents({}) + behavior_db.user_behavior.count_documents({}):,} 条")
    
    # 书评评分分布
    print(f"\n⭐ 书评评分分布:")
    for rating in [5, 4, 3]:
        count = review_db.tb_review.count_documents({'rating': rating})
        total = review_db.tb_review.count_documents({})
        percentage = (count / total * 100) if total > 0 else 0
        stars = '⭐' * rating
        print(f"  {stars} ({rating}星): {count:3d} ({percentage:5.1f}%)")
    
    # 平均评分
    pipeline = [{"$group": {"_id": None, "avg_rating": {"$avg": "$rating"}}}]
    result = list(review_db.tb_review.aggregate(pipeline))
    if result:
        print(f"\n  平均评分: {result[0]['avg_rating']:.2f} 星")
    
    # 用户行为分布
    print(f"\n📈 用户行为类型分布:")
    pipeline = [
        {"$group": {"_id": "$behavior_type", "count": {"$sum": 1}}},
        {"$sort": {"count": -1}}
    ]
    for result in behavior_db.user_behavior.aggregate(pipeline):
        behavior_type = result['_id']
        count = result['count']
        total = behavior_db.user_behavior.count_documents({})
        percentage = (count / total * 100) if total > 0 else 0
        print(f"  • {behavior_type:15s}: {count:5d} ({percentage:5.1f}%)")
    
    print("\n" + "=" * 70)
    print(" " * 25 + "✓ 生成完成！")
    print("=" * 70)
    
    print("\n📌 下一步:")
    print("  1. 启动后端服务")
    print("  2. 测试阅读数据分析功能")
    print("  3. 查看数据可视化效果")
    
    print("\n🔍 验证数据:")
    print("  # 查看笔记")
    print("  mongosh 192.168.200.131:27017/note_db")
    print("  > db.tb_note.count()")
    print("\n  # 查看书评")
    print("  mongosh 192.168.200.131:27017/review_db")
    print("  > db.tb_review.find({rating: 5}).limit(3)")
    print("\n  # 查看用户行为")
    print("  mongosh 192.168.200.131:27017/behavior_db")
    print("  > db.user_behavior.count()")
    
    mongo_client.close()


if __name__ == '__main__':
    try:
        main()
    except KeyboardInterrupt:
        print("\n\n操作已取消")
        sys.exit(0)
    except Exception as e:
        print(f"\n\n错误: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)

